{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Looking in indexes: https://mirror.baidu.com/pypi/simple/\n",
      "Collecting pymysql\n",
      "\u001b[?25l  Downloading https://mirror.baidu.com/pypi/packages/1a/ea/dd9c81e2d85efd03cfbf808736dd055bd9ea1a78aea9968888b1055c3263/PyMySQL-0.10.1-py2.py3-none-any.whl (47kB)\n",
      "\u001b[K     |████████████████████████████████| 51kB 33.3MB/s eta 0:00:01\n",
      "\u001b[?25hInstalling collected packages: pymysql\n",
      "Successfully installed pymysql-0.10.1\n",
      "Looking in indexes: https://mirror.baidu.com/pypi/simple/\n",
      "Collecting sqlalchemy\n",
      "\u001b[?25l  Downloading https://mirror.baidu.com/pypi/packages/a7/91/f4f202d214849d071d4d4481176b09a3ffb9299ca33a8775629cce47daec/SQLAlchemy-1.3.20-cp37-cp37m-manylinux2010_x86_64.whl (1.3MB)\n",
      "\u001b[K     |████████████████████████████████| 1.3MB 12.3MB/s eta 0:00:01\n",
      "\u001b[?25hInstalling collected packages: sqlalchemy\n",
      "Successfully installed sqlalchemy-1.3.20\n"
     ]
    }
   ],
   "source": [
    "!pip install pymysql"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "**从数据库中导入爬取好的数据**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                 id         zishu       tuijian       yuepiao        dianji  \\\n",
      "count  51217.000000  5.121700e+04  5.782000e+03  35527.000000  4.429200e+04   \n",
      "mean   26023.934475  5.897854e+04  9.402748e+02      5.972443  1.560415e+05   \n",
      "std    14896.895814  1.285525e+05  2.962910e+04     55.708122  3.129376e+05   \n",
      "min       91.000000  0.000000e+00  0.000000e+00      0.000000  0.000000e+00   \n",
      "25%    13265.000000  6.468000e+03  0.000000e+00      0.000000  8.510000e+02   \n",
      "50%    26078.000000  1.388800e+04  0.000000e+00      0.000000  9.355900e+04   \n",
      "75%    38885.000000  4.493700e+04  7.000000e+00      0.000000  1.489882e+05   \n",
      "max    51762.000000  3.410598e+06  1.827400e+06   2774.000000  7.905736e+06   \n",
      "\n",
      "            dashang       xianhua    yuanBookId       jReview     jFavorite  \\\n",
      "count  3.552700e+04  3.552700e+04  5.121700e+04  5.121700e+04  5.121700e+04   \n",
      "mean   1.007914e+04  6.557176e+03  1.162120e+08  7.742447e+01  1.192759e+02   \n",
      "std    7.454425e+04  5.140783e+04  3.223992e+08  8.511745e+03  8.233459e+03   \n",
      "min    0.000000e+00  0.000000e+00  1.000000e+00  0.000000e+00  0.000000e+00   \n",
      "25%    0.000000e+00  3.000000e+00  8.219000e+05  0.000000e+00  0.000000e+00   \n",
      "50%    0.000000e+00  8.000000e+01  8.418410e+05  0.000000e+00  0.000000e+00   \n",
      "75%    1.000000e+02  7.490000e+02  1.990390e+06  0.000000e+00  0.000000e+00   \n",
      "max    3.297000e+06  5.007164e+06  1.020086e+09  1.896627e+06  1.665179e+06   \n",
      "\n",
      "             jGifts        jPoint  \n",
      "count  5.121700e+04  5.121700e+04  \n",
      "mean   1.334220e+02  2.551184e+06  \n",
      "std    1.893055e+04  1.790442e+08  \n",
      "min    0.000000e+00  0.000000e+00  \n",
      "25%    0.000000e+00  0.000000e+00  \n",
      "50%    0.000000e+00  0.000000e+00  \n",
      "75%    0.000000e+00  0.000000e+00  \n",
      "max    4.231312e+06  3.784323e+10  \n"
     ]
    }
   ],
   "source": [
    "import pymysql\r\n",
    "import pandas as pd\r\n",
    "import numpy as np\r\n",
    "import matplotlib.pyplot as plt\r\n",
    "%matplotlib inline\r\n",
    "class mysql:#连接数据库的类\r\n",
    "    db=pymysql.connect\r\n",
    "    def __init__(self,url,username,pwd,dbname):\r\n",
    "        self.db = pymysql.connect(url,username,pwd,dbname)#初始化连接\r\n",
    "    def __del__(self):\r\n",
    "        self.db.close()\r\n",
    "    def do(self,sql):\r\n",
    "        try:\r\n",
    "            cursor = self.db.cursor()\r\n",
    "            cursor.execute(sql)\r\n",
    "            self.db.commit()\r\n",
    "            return cursor.fetchall()\r\n",
    "        except Exception:\r\n",
    "            print(Exception)\r\n",
    "a=mysql(\"markchen7788.xyz\", \"cq\", \"123456\", \"novel\")#建立数据库连接对象\r\n",
    "novels=pd.read_sql(\"select * from novel \",a.db)#读取所有爬取的小说集\r\n",
    "a.__del__#释放资源\r\n",
    "print(novels.describe())#查看dataframe数值量的统计情况"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "**分别对三个网站爬取的数据进行清洗，主要包括去除重复数据、去除含有空值的数据、去除异常数据、统一三个网站小说的类型**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/conda/envs/python35-paddle120-env/lib/python3.7/site-packages/ipykernel_launcher.py:19: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "/opt/conda/envs/python35-paddle120-env/lib/python3.7/site-packages/ipykernel_launcher.py:20: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "/opt/conda/envs/python35-paddle120-env/lib/python3.7/site-packages/ipykernel_launcher.py:22: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "/opt/conda/envs/python35-paddle120-env/lib/python3.7/site-packages/ipykernel_launcher.py:25: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "/opt/conda/envs/python35-paddle120-env/lib/python3.7/site-packages/ipykernel_launcher.py:26: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "/opt/conda/envs/python35-paddle120-env/lib/python3.7/site-packages/ipykernel_launcher.py:27: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "/opt/conda/envs/python35-paddle120-env/lib/python3.7/site-packages/ipykernel_launcher.py:28: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "/opt/conda/envs/python35-paddle120-env/lib/python3.7/site-packages/ipykernel_launcher.py:31: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "/opt/conda/envs/python35-paddle120-env/lib/python3.7/site-packages/ipykernel_launcher.py:32: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "/opt/conda/envs/python35-paddle120-env/lib/python3.7/site-packages/ipykernel_launcher.py:35: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "/opt/conda/envs/python35-paddle120-env/lib/python3.7/site-packages/ipykernel_launcher.py:36: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "/opt/conda/envs/python35-paddle120-env/lib/python3.7/site-packages/ipykernel_launcher.py:39: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "/opt/conda/envs/python35-paddle120-env/lib/python3.7/site-packages/ipykernel_launcher.py:41: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "/opt/conda/envs/python35-paddle120-env/lib/python3.7/site-packages/ipykernel_launcher.py:42: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "/opt/conda/envs/python35-paddle120-env/lib/python3.7/site-packages/ipykernel_launcher.py:43: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "/opt/conda/envs/python35-paddle120-env/lib/python3.7/site-packages/ipykernel_launcher.py:44: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "/opt/conda/envs/python35-paddle120-env/lib/python3.7/site-packages/ipykernel_launcher.py:46: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "/opt/conda/envs/python35-paddle120-env/lib/python3.7/site-packages/ipykernel_launcher.py:47: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "/opt/conda/envs/python35-paddle120-env/lib/python3.7/site-packages/ipykernel_launcher.py:48: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "/opt/conda/envs/python35-paddle120-env/lib/python3.7/site-packages/ipykernel_launcher.py:51: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "/opt/conda/envs/python35-paddle120-env/lib/python3.7/site-packages/ipykernel_launcher.py:52: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "        数量        占比%\n",
      "都市    9047  28.111115\n",
      "玄幻奇幻  8446  26.243669\n",
      "同人小说  5226  16.238387\n",
      "军事历史  2276   7.072057\n",
      "科幻    1672   5.195289\n",
      "悬疑    1655   5.142467\n",
      "仙侠    1332   4.138831\n",
      "游戏竞技   771   2.395675\n",
      "女频言情   583   1.811515\n",
      "轻小说    494   1.534972\n",
      "武侠     299   0.929062\n",
      "女频玄幻   135   0.419476\n",
      "女频古代   110   0.341795\n",
      "短篇      78   0.242364\n",
      "女频仙侠    55   0.170898\n",
      "女频同人     4   0.012429\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/conda/envs/python35-paddle120-env/lib/python3.7/site-packages/ipykernel_launcher.py:54: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n"
     ]
    }
   ],
   "source": [
    "no=novels.drop('id',axis=1)#去除id字段\r\n",
    "\r\n",
    "no.drop_duplicates(inplace=True)#去除重复数据\r\n",
    "'''\r\n",
    "对飞卢网站的小说数据集进行清洗\r\n",
    "'''\r\n",
    "\r\n",
    "feilu=no[no['website']=='飞卢']\r\n",
    "feilu=feilu.drop(['tuijian','jReview','jFavorite','jGifts','jPoint'],axis=1)#删除与飞卢小说网无关的字段\r\n",
    "feilu=feilu.dropna()#去除含有空值的字段\r\n",
    "feilu=feilu[feilu['zishu']>=3000]#去除字数少于3000的数据集\r\n",
    "\r\n",
    "feilu.insert(6,'n_type',value='')#插入一个新字段\r\n",
    "\r\n",
    "feilu.n_type=feilu.type#将原分类字段数据赋值给新分类字段\r\n",
    "\r\n",
    "#根据需求将原有小说分类转换成统一的新分类\r\n",
    "#都市言情、青春校园归为都市，科幻网游归为科幻\r\n",
    "feilu.n_type[feilu['n_type']=='都市言情']='都市'\r\n",
    "feilu.n_type[feilu['n_type']=='青春校园']='都市'\r\n",
    "\r\n",
    "feilu.n_type[feilu['n_type']=='科幻网游']='科幻'\r\n",
    "\r\n",
    "#将科幻类中标签里含有网游、NBA、明星、直播字眼的书归类为游戏竞技\r\n",
    "feilu.n_type[(feilu['n_type']=='科幻')&(feilu['tag'].str.contains('网游'))]='游戏竞技'\r\n",
    "feilu.n_type[(feilu['n_type']=='科幻')&(feilu['tag'].str.contains('NBA'))]='游戏竞技'\r\n",
    "feilu.n_type[(feilu['n_type']=='科幻')&(feilu['tag'].str.contains('明星'))]='游戏竞技'\r\n",
    "feilu.n_type[(feilu['n_type']=='科幻')&(feilu['tag'].str.contains('直播'))]='游戏竞技'\r\n",
    "\r\n",
    "#将武侠仙侠类小说中标签含有武侠的小说归类为武侠，其余归类仙侠\r\n",
    "feilu.n_type[feilu['n_type']=='武侠仙侠']='仙侠'\r\n",
    "feilu.n_type[(feilu['n_type']=='仙侠')&(feilu['tag'].str.contains('武侠'))]='武侠'\r\n",
    "\r\n",
    "#短篇其它归类为短篇，恐怖灵异归类为悬疑\r\n",
    "feilu.n_type[feilu['n_type']=='短篇其他']='短篇'\r\n",
    "feilu.n_type[feilu['n_type']=='恐怖灵异']='悬疑'\r\n",
    "\r\n",
    "#将女生小说根据标签的关键字进一步划分为更细致的类型\r\n",
    "feilu.n_type[feilu['n_type']=='女生小说']='女频言情'\r\n",
    "\r\n",
    "feilu.n_type[(feilu['n_type']=='女频言情')&(feilu['tag'].str.contains('无限'))]='女频玄幻'\r\n",
    "feilu.n_type[(feilu['n_type']=='女频言情')&(feilu['tag'].str.contains('玄幻'))]='女频玄幻'\r\n",
    "feilu.n_type[(feilu['n_type']=='女频言情')&(feilu['tag'].str.contains('位面'))]='女频玄幻'\r\n",
    "feilu.n_type[(feilu['n_type']=='女频言情')&(feilu['tag'].str.contains('异界'))]='女频玄幻'\r\n",
    "\r\n",
    "feilu.n_type[(feilu['n_type']=='女频言情')&(feilu['tag'].str.contains('种田'))]='女频古代'\r\n",
    "feilu.n_type[(feilu['n_type']=='女频言情')&(feilu['tag'].str.contains('穿越'))]='女频古代'\r\n",
    "feilu.n_type[(feilu['n_type']=='女频言情')&(feilu['tag'].str.contains('权谋'))]='女频古代'\r\n",
    "\r\n",
    "\r\n",
    "feilu.n_type[(feilu['n_type']=='女频言情')&(feilu['tag'].str.contains('修真'))]='女频仙侠'\r\n",
    "feilu.n_type[(feilu['n_type']=='女频言情')&(feilu['tag'].str.contains('修仙'))]='女频仙侠'\r\n",
    "\r\n",
    "feilu.n_type[(feilu['n_type']=='女频言情')&(feilu['tag'].str.contains('明星'))]='女频同人'\r\n",
    "\r\n",
    "#统计新类型各种书籍的数量以及所占比例\r\n",
    "aa=feilu.n_type.value_counts()\r\n",
    "bb=aa*100/feilu.shape[0]\r\n",
    "\r\n",
    "statistic=pd.DataFrame({'数量':aa,'占比%':bb})\r\n",
    "print(statistic)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/conda/envs/python35-paddle120-env/lib/python3.7/site-packages/ipykernel_launcher.py:15: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  from ipykernel import kernelapp as app\n",
      "/opt/conda/envs/python35-paddle120-env/lib/python3.7/site-packages/ipykernel_launcher.py:18: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "/opt/conda/envs/python35-paddle120-env/lib/python3.7/site-packages/ipykernel_launcher.py:19: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "/opt/conda/envs/python35-paddle120-env/lib/python3.7/site-packages/ipykernel_launcher.py:22: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "/opt/conda/envs/python35-paddle120-env/lib/python3.7/site-packages/ipykernel_launcher.py:23: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "女频言情    4526\n",
      "女频同人    2258\n",
      "女频玄幻    1906\n",
      "女频古代     449\n",
      "悬疑        75\n",
      "女频仙侠      26\n",
      "Name: n_type, dtype: int64\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/conda/envs/python35-paddle120-env/lib/python3.7/site-packages/ipykernel_launcher.py:26: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n"
     ]
    }
   ],
   "source": [
    "'''\r\n",
    "对晋江网站的小说数据集进行清洗\r\n",
    "'''\r\n",
    "jinjiang=no[no['website']=='晋江']\r\n",
    "jinjiang=jinjiang.drop(['tuijian','yuepiao','dianji','dashang','xianhua'],axis=1)#删除与晋江小说网无关的字段\r\n",
    "jinjiang=jinjiang.dropna()#去空值\r\n",
    "jinjiang=jinjiang[jinjiang['zishu']>=500]#去除字数少于500字的小说\r\n",
    "\r\n",
    "#插入“新类型“字段\r\n",
    "jinjiang.insert(6,'n_type',value='女频言情')\r\n",
    "\r\n",
    "#原有分类中含有短篇字眼的归类为短篇，含有悬疑、惊悚的归为悬疑\r\n",
    "jinjiang.n_type[jinjiang['type'].str.contains('短篇')]='短篇'\r\n",
    "jinjiang.n_type[jinjiang['type'].str.contains('悬疑')]='悬疑'\r\n",
    "jinjiang.n_type[jinjiang['type'].str.contains('惊悚')]='悬疑'\r\n",
    "\r\n",
    "#原有分类中含有仙侠、武侠的归为女频仙侠\r\n",
    "jinjiang.n_type[jinjiang['type'].str.contains('仙侠')]='女频仙侠'\r\n",
    "jinjiang.n_type[jinjiang['type'].str.contains('武侠')]='女频仙侠'\r\n",
    "\r\n",
    "#含有架空历史的归类为女频玄幻，含有古色古香的归为女频古代\r\n",
    "jinjiang.n_type[jinjiang['type'].str.contains('架空历史')]='女频玄幻'\r\n",
    "jinjiang.n_type[jinjiang['type'].str.contains('古色古香')]='女频古代'\r\n",
    "\r\n",
    "#含有衍生的归为女频同人\r\n",
    "jinjiang.n_type[jinjiang['type'].str.contains('衍生')]='女频同人'\r\n",
    "\r\n",
    "print(jinjiang.n_type.value_counts())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/conda/envs/python35-paddle120-env/lib/python3.7/site-packages/ipykernel_launcher.py:17: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "/opt/conda/envs/python35-paddle120-env/lib/python3.7/site-packages/ipykernel_launcher.py:18: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "/opt/conda/envs/python35-paddle120-env/lib/python3.7/site-packages/ipykernel_launcher.py:19: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "/opt/conda/envs/python35-paddle120-env/lib/python3.7/site-packages/ipykernel_launcher.py:22: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "/opt/conda/envs/python35-paddle120-env/lib/python3.7/site-packages/ipykernel_launcher.py:23: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "/opt/conda/envs/python35-paddle120-env/lib/python3.7/site-packages/ipykernel_launcher.py:24: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "/opt/conda/envs/python35-paddle120-env/lib/python3.7/site-packages/ipykernel_launcher.py:27: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "/opt/conda/envs/python35-paddle120-env/lib/python3.7/site-packages/ipykernel_launcher.py:28: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "/opt/conda/envs/python35-paddle120-env/lib/python3.7/site-packages/ipykernel_launcher.py:30: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "/opt/conda/envs/python35-paddle120-env/lib/python3.7/site-packages/ipykernel_launcher.py:31: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "/opt/conda/envs/python35-paddle120-env/lib/python3.7/site-packages/ipykernel_launcher.py:34: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "/opt/conda/envs/python35-paddle120-env/lib/python3.7/site-packages/ipykernel_launcher.py:35: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "/opt/conda/envs/python35-paddle120-env/lib/python3.7/site-packages/ipykernel_launcher.py:37: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "/opt/conda/envs/python35-paddle120-env/lib/python3.7/site-packages/ipykernel_launcher.py:40: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "/opt/conda/envs/python35-paddle120-env/lib/python3.7/site-packages/ipykernel_launcher.py:41: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "/opt/conda/envs/python35-paddle120-env/lib/python3.7/site-packages/ipykernel_launcher.py:44: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "/opt/conda/envs/python35-paddle120-env/lib/python3.7/site-packages/ipykernel_launcher.py:45: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "/opt/conda/envs/python35-paddle120-env/lib/python3.7/site-packages/ipykernel_launcher.py:46: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "/opt/conda/envs/python35-paddle120-env/lib/python3.7/site-packages/ipykernel_launcher.py:49: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "/opt/conda/envs/python35-paddle120-env/lib/python3.7/site-packages/ipykernel_launcher.py:50: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "/opt/conda/envs/python35-paddle120-env/lib/python3.7/site-packages/ipykernel_launcher.py:53: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "/opt/conda/envs/python35-paddle120-env/lib/python3.7/site-packages/ipykernel_launcher.py:55: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "玄幻奇幻    1543\n",
      "短篇       572\n",
      "都市       530\n",
      "女频言情     434\n",
      "科幻       388\n",
      "轻小说      347\n",
      "仙侠       324\n",
      "同人小说     268\n",
      "悬疑       240\n",
      "女频古代     203\n",
      "游戏竞技     174\n",
      "武侠       144\n",
      "女频玄幻     138\n",
      "军事历史     132\n",
      "女频仙侠      57\n",
      "女频同人      18\n",
      "Name: n_type, dtype: int64\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/conda/envs/python35-paddle120-env/lib/python3.7/site-packages/ipykernel_launcher.py:57: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "/opt/conda/envs/python35-paddle120-env/lib/python3.7/site-packages/ipykernel_launcher.py:58: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "/opt/conda/envs/python35-paddle120-env/lib/python3.7/site-packages/ipykernel_launcher.py:59: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n"
     ]
    }
   ],
   "source": [
    "'''\r\n",
    "对起点网站的小说数据集进行清洗\r\n",
    "'''\r\n",
    "qidian=no[no['website']=='起点']\r\n",
    "qidian=qidian.drop(['yuepiao','dianji','dashang','xianhua','jReview','jFavorite','jGifts','jPoint'],axis=1)#删除与起点小说网无关的字段\r\n",
    "qidian=qidian.dropna()#去空值\r\n",
    "qidian=qidian[qidian['zishu']>=1000]#去除字数少于1000字的小说\r\n",
    "\r\n",
    "\r\n",
    "#插入“新类型“字段\r\n",
    "qidian.insert(6,'n_type',value='')\r\n",
    "#军事、历史合并\r\n",
    "qidian.n_type[qidian['type']=='军事']='军事历史'\r\n",
    "qidian.n_type[qidian['type']=='历史']='军事历史'\r\n",
    "\r\n",
    "#都市、现实、现实生活合并为都市\r\n",
    "qidian.n_type[qidian['type']=='都市']='都市'\r\n",
    "qidian.n_type[qidian['type']=='现实']='都市'\r\n",
    "qidian.n_type[qidian['type']=='现实生活']='都市'\r\n",
    "\r\n",
    "#游戏、体育、游戏竞技合并为游戏竞技\r\n",
    "qidian.n_type[qidian['type']=='游戏']='游戏竞技'\r\n",
    "qidian.n_type[qidian['type']=='体育']='游戏竞技'\r\n",
    "qidian.n_type[qidian['type']=='游戏竞技']='游戏竞技'\r\n",
    "\r\n",
    "#科幻、科幻空间合并为科幻\r\n",
    "qidian.n_type[qidian['type']=='科幻']='科幻'\r\n",
    "qidian.n_type[qidian['type']=='科幻空间']='科幻'\r\n",
    "\r\n",
    "qidian.n_type[qidian['type']=='武侠']='武侠'\r\n",
    "qidian.n_type[qidian['type']=='仙侠']='仙侠'\r\n",
    "\r\n",
    "#玄幻、奇幻合并\r\n",
    "qidian.n_type[qidian['type']=='玄幻']='玄幻奇幻'\r\n",
    "qidian.n_type[qidian['type']=='奇幻']='玄幻奇幻'\r\n",
    "\r\n",
    "qidian.n_type[qidian['type']=='短篇']='短篇'\r\n",
    "\r\n",
    "#悬疑、悬疑推理合并\r\n",
    "qidian.n_type[qidian['type']=='悬疑']='悬疑'\r\n",
    "qidian.n_type[qidian['type']=='悬疑推理']='悬疑'\r\n",
    "\r\n",
    "\r\n",
    "qidian.n_type[qidian['type']=='玄幻言情']='女频玄幻'\r\n",
    "qidian.n_type[qidian['type']=='古代言情']='女频古代'\r\n",
    "qidian.n_type[qidian['type']=='仙侠奇缘']='女频仙侠'\r\n",
    "\r\n",
    "#现代言情、浪漫青春合并为女频言情\r\n",
    "qidian.n_type[qidian['type']=='现代言情']='女频言情'\r\n",
    "qidian.n_type[qidian['type']=='浪漫青春']='女频言情'\r\n",
    "\r\n",
    "#轻小说进一步细化\r\n",
    "qidian.n_type[qidian['type']=='轻小说']='轻小说'\r\n",
    "\r\n",
    "qidian.n_type[(qidian['type']=='轻小说')&(qidian['tag'].str.contains('衍生'))]='同人小说'\r\n",
    "\r\n",
    "qidian.n_type[(qidian['type']=='轻小说')&(qidian['tag'].str.contains('同人衍生'))]='女频同人'\r\n",
    "qidian.n_type[(qidian['type']=='轻小说')&(qidian['tag'].str.contains('古典衍生'))]='女频同人'\r\n",
    "qidian.n_type[(qidian['type']=='轻小说')&(qidian['tag'].str.contains('唯美幻想'))]='女频同人'\r\n",
    "\r\n",
    "print(qidian.n_type.value_counts())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "**将清洗好的数据持久化（存入数据库）**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "name                  object\n",
      "zishu                  int64\n",
      "zuozhe                object\n",
      "website               object\n",
      "type                  object\n",
      "tag                   object\n",
      "n_type                object\n",
      "startTime     datetime64[ns]\n",
      "endTime       datetime64[ns]\n",
      "yuepiao              float64\n",
      "dianji               float64\n",
      "dashang              float64\n",
      "xianhua              float64\n",
      "yuanBookId             int64\n",
      "dtype: object\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "<bound method mysql.__del__ of <__main__.mysql object at 0x7f06730787d0>>"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "'''\r\n",
    "将清洗好的飞卢网小说数据持久化（存入数据库）\r\n",
    "'''\r\n",
    "print(feilu.dtypes)\r\n",
    "feilu.startTime=feilu.startTime.astype('str')#时间戳字段类型改为字符串\r\n",
    "feilu.endTime=feilu.endTime.astype('str')\r\n",
    "temp=feilu.values.tolist()#将dataframe转换为list\r\n",
    "newdb=mysql(\"47.94.92.138\", \"cq\", \"123456\", \"_novel\")\r\n",
    "for i in temp:\r\n",
    "    sql='insert into novel(name,zishu,zuozhe,website,type,tag,n_type,startTime,endTime,yuepiao,dianji,dashang,xianhua,yuanBookId) values(\"{}\",\"{}\",\"{}\",\"{}\",\"{}\",\"{}\",\"{}\",\"{}\",\"{}\",\"{}\",\"{}\",\"{}\",\"{}\",\"{}\")'.format(i[0],i[1],i[2],i[3],i[4],i[5],i[6],i[7],i[8],i[9],i[10],i[11],i[12],i[13])\r\n",
    "    newdb.do(sql)\r\n",
    "newdb.__del__"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<bound method mysql.__del__ of <__main__.mysql object at 0x7f066c35f0d0>>"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "'''\r\n",
    "将清洗好的起点网小说数据持久化（存入数据库）\r\n",
    "'''\r\n",
    "qidian.startTime=qidian.startTime.astype('str')\r\n",
    "qidian.endTime=qidian.endTime.astype('str')\r\n",
    "temp=qidian.values.tolist()\r\n",
    "newdb=mysql(\"47.94.92.138\", \"cq\", \"123456\", \"_novel\")\r\n",
    "for i in temp:\r\n",
    "    sql='insert into novel(name,zishu,zuozhe,website,type,tag,n_type,startTime,endTime,tuijian,yuanBookId) values(\"{}\",\"{}\",\"{}\",\"{}\",\"{}\",\"{}\",\"{}\",\"{}\",\"{}\",\"{}\",\"{}\")'.format(i[0],i[1],i[2],i[3],i[4],i[5],i[6],i[7],i[8],i[9],i[10])\r\n",
    "    newdb.do(sql)\r\n",
    "newdb.__del__"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<bound method mysql.__del__ of <__main__.mysql object at 0x7f066ca16c90>>"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "'''\r\n",
    "将清洗好的晋江网小说数据持久化（存入数据库）\r\n",
    "'''\r\n",
    "jinjiang.startTime=jinjiang.startTime.astype('str')\r\n",
    "jinjiang.endTime=jinjiang.endTime.astype('str')\r\n",
    "temp=jinjiang.values.tolist()\r\n",
    "newdb=mysql(\"47.94.92.138\", \"cq\", \"123456\", \"_novel\")\r\n",
    "for i in temp:\r\n",
    "    sql='insert into novel(name,zishu,zuozhe,website,type,tag,n_type,startTime,endTime,jReview,jFavorite,jGifts,jPoint,yuanBookId) values(\"{}\",\"{}\",\"{}\",\"{}\",\"{}\",\"{}\",\"{}\",\"{}\",\"{}\",\"{}\",\"{}\",\"{}\",\"{}\",\"{}\")'.format(i[0],i[1],i[2],i[3],i[4],i[5],i[6],i[7],i[8],i[9],i[10],i[11],i[12],i[13])\r\n",
    "    #newdb.do(sql)\r\n",
    "newdb.__del__"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "PaddlePaddle 1.8.4 (Python 3.5)",
   "language": "python",
   "name": "py35-paddle1.2.0"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}
